/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ortustve.onlinescrapper.tools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import ortustve.onlinescrapper.MediaObject;
import ortustve.onlinescrapper.themoviedb.Movie;
import ortustve.onlinescrapper.thetvdb.Actor;
import ortustve.onlinescrapper.thetvdb.Episode;
import ortustve.onlinescrapper.thetvdb.Series;
import sagex.api.AiringAPI;
import sagex.api.MediaFileAPI;
import sagex.api.ShowAPI;

/**
 *
 * @author jphipps
 */
public class database extends ortustve.ortusvars {
    public static void LogFind(int scantype, int mediaid, String searchtitle, HashMap<String,String> resultTitles) {
        ortustve.api.executeSQL("delete from sage.scrapperlog where scantype = " + scantype + " and mediaid = " + mediaid);
         Object[] mk = resultTitles.keySet().toArray();
        for ( Object mt : mk ) {
            ortustve.api.executeSQL("insert into sage.scrapperlog (scantype, mediaid, searchtitle, foundtitle, foundkey, scandate) values( " + scantype + "," + mediaid + ",'" + searchtitle.replaceAll("'","''") + "','" + ((String)mt).replaceAll("'","''") + "','" + resultTitles.get(mt).replaceAll("'","''") + "',current_timestamp)");
        }

        if ( mk.length == 0) {
            ortustve.api.executeSQL("insert into sage.scrapperlog (scantype, mediaid, searchtitle, foundtitle, foundkey, scandate) values( " + scantype + "," + mediaid + ",'" + searchtitle.replaceAll("'","''") + "','None Found','None Found',current_timestamp)");
        }
    }

    public static Movie GetCacheMetadataIMDB(String imdbid) {
        return GetCacheMetadata("imdbid",imdbid);
    }

    public static Movie GetCacheMetadataTMDB(String tmdbid) {
        return GetCacheMetadata("tmdbid",tmdbid);
    }

    private static Movie GetCacheMetadata(String keytype, String key) {
//        ortus.api.DebugLog(TRACE2,"GetCacheMetadata: Searching cache for type: " + keytype + " key: " + key.trim());
        Connection conn = ortustve.api.GetConnection();
        String sql = "select mediaobject from sage.metadatacache where " + keytype + " = ?";
        try {
          Movie cacheitem = null;
          PreparedStatement stmt = conn.prepareStatement(sql);
          stmt.setString(1,key.trim());
          ResultSet rs = stmt.executeQuery();
          if  ( rs.next() ) {
              cacheitem = (Movie)rs.getObject(1);
          }
          stmt.close();
//	  conn.close();
          if ( cacheitem != null )
              ortustve.api.DebugLog(TRACE2," GetMetadataCache: Found: key: " + key + " in cache");
          return cacheitem;
        } catch(Exception e ) {
            ortustve.api.DebugLog(ERROR,"GetCacheMetadata: SQL: " + sql);
            ortustve.api.DebugLog( ERROR, "GetCacheMetadata: SQLException: " + e);
            return null;
        }
    }
    public static void cacheMetadata(Movie mo) {
//	ortus.api.DebugLog(TRACE2,"cacheMetadata:  caching tmdbid: " + mo.GetId() + " imdbid: " + mo.GetIMDBId());
        Connection conn = ortustve.api.GetConnection();

        List<Object> result = ortustve.api.executeSQLQuery("select imdbid from sage.metadatacache where imdbid = '" + mo.GetIMDBId() + "' and tmdbid = '" + mo.GetId() + "'");
        if ( result.size() > 0) {
            return;
        }
        String sql = "insert into sage.metadatacache ( tmdbid, imdbid, title, mediaobject ) values(?,?,?,?)";
        try {
          PreparedStatement stmt = conn.prepareStatement(sql);
          stmt.setString(1,mo.GetId());
          stmt.setString(2,mo.GetIMDBId());
          stmt.setString(3,mo.GetName());
          stmt.setObject(4, mo);
          stmt.execute();
          stmt.close();
//	  conn.close();
        } catch(Exception e ) {
            ortustve.api.DebugLog(ERROR,"cacheMetadata: SQL: " + sql);
            ortustve.api.DebugLog( ERROR, "cacheMetadata: SQLException: " + e);
            return;
        }
        return;
    }

    public static void WriteMediatoDB(MediaObject mo) {
       String name = mo.GetShowTitle().replaceAll("'","''");
       int mfid = MediaFileAPI.GetMediaFileID(mo.GetMedia());

       String runtime = String.valueOf(MediaFileAPI.GetFileDuration(mo.GetMedia()));
       if ( runtime.isEmpty())
           runtime="0";

       String SQL ="UPDATE sage.media SET mediatitle='"+ name +"', episodetitle = '" + ShowAPI.GetShowEpisode(mo.GetMedia()).replaceAll("'","''") + "', mediapath='" + MediaFileAPI.GetFileForSegment(mo.GetMedia(),0).getAbsolutePath().replaceAll("'","''") + "', mediaencoding = '" + MediaFileAPI.GetMediaFileFormatDescription(mo.GetMedia()).trim() + "', mediasize = " + MediaFileAPI.GetSize(mo.GetMedia()) + ", mediaduration = " + runtime + " , lastwatchedtime = " +
                AiringAPI.GetLatestWatchedTime(mo.GetMedia()) + " , airingstarttime = " + AiringAPI.GetAiringStartTime(mo.GetMedia()) + ", mediagroup = " + mo.GetMediaGroup() + "  WHERE mediaid = " + String.valueOf(mfid);
        int success = ortustve.api.executeSQL(SQL);
        if(success < 1){
            SQL =  "INSERT INTO sage.media (mediaid, mediatitle, episodetitle, mediapath, mediaencoding, mediatype, mediagroup, mediasize, mediaduration, lastwatchedtime, airingstarttime, mediaimporttime) " +
                       " VALUES(" + mfid + ", '" + name + "','" + ShowAPI.GetShowEpisode(mo.GetMedia()).replaceAll("'","''") + "','" + MediaFileAPI.GetFileForSegment(mo.GetMedia(),0).getAbsolutePath().replaceAll("'","''") + "','" + MediaFileAPI.GetMediaFileFormatDescription(mo.GetMedia()).trim() + "',0, " + mo.GetMediaGroup() + ", " + MediaFileAPI.GetSize(mo.GetMedia()) +
                       ", " + MediaFileAPI.GetFileDuration(mo.GetMedia()) + ", " + AiringAPI.GetLatestWatchedTime(mo.GetMedia()) + "," + AiringAPI.GetAiringStartTime(mo.GetMedia()) + ", current_timestamp)";
            ortustve.api.executeSQL(SQL);
        }
    }

    public static void WriteEpisodetoDB(Episode Episode,String SeriesTitle){
        String Description =  Episode.getOverview().replaceAll("'", "''");
        String EpisodeName = Episode.getEpisodeName().replaceAll("'", "''");
        float workrating = 0;
        try { workrating = Float.parseFloat(Episode.getRating()); } catch(Exception e) {}
        if ( workrating == 0) workrating = 5;

        String SQL ="UPDATE sage.episode SET title ='" +EpisodeName +"',description ='" +
                Description +"',originalairdate='" + Episode.getFirstAired()+"',userrating='" +Episode.getRating() +
                "',seasonno='" + Episode.getSeasonNumber() +"',episodeno =" +Episode.getEpisodeNumber() +
                " WHERE seriesid = " + Episode.getSeriesId() + " and seasonid = " + Episode.getSeasonId() + " and episodeid = " + Episode.getId();
        int success = ortustve.api.executeSQL(SQL);
        if(success < 1){
            SQL =  "INSERT INTO sage.episode (seriesid, episodeid, seasonid, episodeno,  title,description,originalairdate,userrating,seasonno )  " +
                    " VALUES(" + Episode.getSeriesId() + "," + Episode.getId() + ", "+ Episode.getSeasonId() + " , " + Episode.getEpisodeNumber() + ", '" + EpisodeName +"','" +
                    Description +"','" + Episode.getFirstAired() +"'," + workrating +
                    "," + Episode.getSeasonNumber() + ")";
            ortustve.api.executeSQL(SQL);
        }

        List<String> directors = Episode.getDirectors();
        for ( String x : directors) {
            ortustve.api.executeSQL("insert into sage.seriescast ( seriesid, episodeid, name, job, character) values ( " + Episode.getSeriesId() + "," + Episode.getId() + ",'" + x.replaceAll("'","''") + "','Director','')");
        }
        List<String> writers = Episode.getWriters();
        for ( String x : writers) {
            ortustve.api.executeSQL("insert into sage.seriescast ( seriesid, episodeid, name, job, character) values ( " + Episode.getSeriesId() + "," + Episode.getId() + ",'" + x.replaceAll("'","''") + "','Writer','')");
        }
        List<String> guests = Episode.getGuestStars();
        for ( String x : guests) {
            ortustve.api.executeSQL("insert into sage.seriescast ( seriesid, episodeid, name, job, character) values ( " + Episode.getSeriesId() + "," + Episode.getId() + ",'" + x.replaceAll("'","''") + "','Guest Star','')");
        }

    }

    public static void WriteSeriestoDB(Series Series, List<Actor> actors) {
        //get 's out of description
        String Description =  Series.getOverview().replaceAll("'", "''");
        // get ' our of episode title
        String Name = Series.getSeriesName().replaceAll("'", "''");
        float workrating = 0;
        try { workrating = Float.parseFloat(Series.getRating()); } catch(Exception e) {}
        if ( workrating == 0) workrating = 5;
        String runtime = Series.getRuntime();
        if ( runtime.isEmpty())
            runtime="0";

        String SQL ="UPDATE sage.series SET title ='" +Name +"',firstair='" +
                Series.getFirstAired() +"',airday='" + Series.getAirsDayOfWeek() +"',status='" +Series.getStatus() +
                "',description='" + Description +"',network='" +Series.getNetwork() +"',userrating=" + workrating +
                ",mpaarated='" + Series.getContentRating() +"',runtime=" + runtime+", imdbid = '" + Series.getImdbId() + "', zap2itid = '" + Series.getZap2ItId() + "', airtime = '" + Series.getAirsTime() + "' WHERE seriesid=" +Series.getId();
        int success = ortustve.api.executeSQL(SQL);
        if(success < 1){
            SQL =  "INSERT INTO sage.series (seriesid,title,firstair,airday,status,description,network,userrating,mpaarated,runtime, imdbid, zap2itid, airtime) " +
                    "VALUES("+ Series.getId() +",'" +Name +"','" + Series.getFirstAired() +"','" + Series.getAirsDayOfWeek() +"','" +Series.getStatus() +
                    "','" + Description +"','" +Series.getNetwork() +"'," + workrating +
                    ",'" + Series.getContentRating() +"'," + runtime+", '" + Series.getImdbId() + "','" + Series.getZap2ItId() + "','" + Series.getAirsTime() + "')";
            ortustve.api.executeSQL(SQL);
        }
        ortustve.api.executeSQL("delete from sage.seriesgenre where seriesid = " + Series.getId());
        for ( String g : Series.getGenres()) {
            ortustve.api.executeSQL("insert into sage.seriesgenre ( seriesid, name ) values ( " + Series.getId() + ",'" + g + "')");
        }
        ortustve.api.executeSQL("delete from sage.seriescast where seriesid = " + Series.getId());
        for ( Actor a : actors) {
            ortustve.api.executeSQL("insert into sage.seriescast ( seriesid, episodeid, name, job, character) values ( " + Series.getId() + ", 0, '" + a.getName().replaceAll("'","''") + "','actor','" + a.getRole().replaceAll("'","''") + "')");
        }
    }
    public static void WriteTVFanart(MediaObject mo, String type, String url , String filename) {
	String SQL = "update sage.fanart set file = '" + filename + "' where mediaid = " + MediaFileAPI.GetMediaFileID(mo.GetMedia()) + " and type = '" + type + "' and url = '" + url + "'";
	int success = ortustve.api.executeSQL(SQL);
	if ( success < 1) {
		SQL =  "INSERT INTO sage.fanart (mediaid, type, url, file) VALUES(" + MediaFileAPI.GetMediaFileID(mo.GetMedia()) + ", '" + type + "','" + url + "','" + filename + "')";
		ortustve.api.executeSQL(SQL);
	}
    }
    
    public static void UpdateEpisodeMediaID(MediaObject mo) {
        ortustve.api.DebugLog(TRACE2,"UpdateEpisodeMediaID: running");
        boolean titleset = true;
        Pattern pattern = Pattern.compile("S(\\d+)E(\\d+)");
        Matcher matcher = pattern.matcher(mo.GetEpisodeTitle());
        int EpisodeNo = 0;
        int SeasonNo = 0;
        List<List> mtv;
        String workSeriesTitle = mo.GetShowTitle().replaceAll("'","''");
        String workEpisodeTitle = mo.GetEpisodeTitle().replaceAll("'","''");

        if ( matcher.matches()) {
            ortustve.api.DebugLog(TRACE2, " UpdateEpisodeID: matcher found: " + matcher.groupCount());
            ortustve.api.DebugLog(TRACE2, " Series found, title: " + mo.GetShowTitle() + " Season:" +  matcher.group(1) + " Episode: " + matcher.group(2));
            titleset = false;
            SeasonNo = Integer.parseInt(matcher.group(1));
            EpisodeNo = Integer.parseInt(matcher.group(2));
            String sql = "select e.seasonno, s.seriesid, e.episodeno, e.title from sage.episode e, sage.series s where s.seriesid = e.seriesid and lower(s.title) = '" + workSeriesTitle.toLowerCase().trim() + "' and e.seasonno = " + SeasonNo + " and e.episodeno = " + EpisodeNo;
            mtv = ortustve.api.executeSQLQueryArray(sql);
        } else {
            mtv = ortustve.api.executeSQLQueryArray("select e.seasonno, s.seriesid, e.episodeno, e.title from sage.episode e, sage.series s where s.seriesid = e.seriesid and lower(s.title) = '" + workSeriesTitle.toLowerCase() + "' and lower(e.title) = '" + workEpisodeTitle.toLowerCase() + "'");
        }

        if ( mtv.size() < 1) {
            ortustve.api.DebugLog(TRACE2, " Series: " + mo.GetShowTitle() + " Episode: " + mo.GetEpisodeTitle() + " not found" );
            return;
        }

//	ortus.api.DebugLog(TRACE2," epid: " + epid.length + " result: " + result);
//        workEpisodeTitle = epid[3].replaceAll("'","''");

        String SQL ="UPDATE sage.episode SET mediaid = " + MediaFileAPI.GetMediaFileID(mo.GetMedia()) + " where seasonno = " + mtv.get(0).get(0) + " and seriesid = " + mtv.get(0).get(1) + " and episodeno = " + mtv.get(0).get(2);
        int success = ortustve.api.executeSQL(SQL);
        if ( success > 0 ) {
            SQL = "update sage.media set mediatype = 3, mediatitle = '" + workSeriesTitle + "',episodetitle = '" + ((String)mtv.get(0).get(3)).replaceAll("'","''") + "' where mediaid = " + MediaFileAPI.GetMediaFileID(mo.GetMedia());
            success = ortustve.api.executeSQL(SQL);
            if ( success > 0 )
                ortustve.api.DebugLog(TRACE2, "UpdateEpisodeMediaID: Successful");
            else
                ortustve.api.DebugLog(TRACE2, "UpdateEpisodeMediaID: Failed media series set");
        } else
            ortustve.api.DebugLog(TRACE2, "UpdateEpisodeMediaID: Failed");
    }
}